---
title: Database Documentation
description: Best practices for effective database documentation including schema docs, query documentation, and operational runbooks
---

# Database Documentation

Comprehensive, well-maintained documentation is the foundation of effective database management. This guide covers best practices for documenting schemas, queries, and operational procedures to ensure knowledge is preserved, accessible, and usable across your organization.

## Schema Documentation

### Table Documentation

Every table should have clear, complete documentation explaining its purpose and structure.

**Table Documentation Template:**

```markdown
# Table: customers

## Business Purpose
Stores core customer information for all accounts in the system.
Used for billing, communications, reporting, and customer service.

## Data Ownership
- Owner: Sales Operations Team
- Stakeholders: Finance, Customer Success, Marketing
- Contact: sales-ops@example.com

## Update Frequency
- Real-time: Customer created/updated through web app
- Batch: Imported from legacy system (monthly reconciliation)
- Last Updated: [system generated timestamp]

## Data Retention Policy
- Active customers: Retained indefinitely
- Inactive customers: Retained for 7 years (compliance requirement)
- Deleted records: Soft delete only, never permanently removed

## Typical Usage
- Active user base: ~500K records
- Monthly growth: 2-3%
- Historical data since: 2015-01-01

## Related Tables
- orders (customer has many orders)
- billing_addresses (customer has multiple)
- support_tickets (customer related)
- analytics_customer_fact (denormalized reporting)

## Known Issues
- Phone number format inconsistent (not validated at input)
- Legacy data includes some null email addresses (~5%)
- Duplicate email entries possible in data from 2015-2016

## Access Restrictions
- PII columns: Restricted to authorized roles
- Email/phone: Masked for read-only users
- Production data: Not available in development environment
```

### Column Documentation

<AccordionGroup>
<Accordion title="Data Type and Constraints">
Document exact data types and constraints:

```markdown
### Column: customer_id
- Type: BIGINT
- Constraint: PRIMARY KEY, NOT NULL, AUTO INCREMENT
- Default: Auto-generated by database
- Range: 1 to 9223372036854775807
```
</Accordion>

<Accordion title="Business Meaning">
Explain what the column represents in business terms:

```markdown
### Column: customer_tier
- Type: VARCHAR(50)
- Business Meaning: Customer subscription level affecting pricing and features
- Valid Values:
  - 'free': Trial users with basic access
  - 'standard': Paid monthly subscription
  - 'professional': Annual commitment, priority support
  - 'enterprise': Custom agreement, dedicated support
- Default: 'free'
- Can be NULL: No
```
</Accordion>

<Accordion title="Update Pattern">
Describe how and when data is updated:

```markdown
### Column: last_login_at
- Type: TIMESTAMP WITH TIME ZONE
- Updated: Every time user logs in (application layer)
- How: Application sets to CURRENT_TIMESTAMP
- Update Frequency: Variable (depends on user activity)
- Time Zone: UTC, always
- Can be NULL: Yes (user never logged in)
- Used For: Account activity tracking, identifying inactive users
```
</Accordion>

<Accordion title="Relationships">
Document foreign keys and relationships:

```markdown
### Column: primary_address_id
- Type: BIGINT
- Foreign Key: references addresses.id
- Relationship: Many customers → One address
- Can be NULL: Yes (address not yet provided)
- Orphan Handling: ON DELETE SET NULL (address deletion)
- Used For: Billing and shipping addresses
- Index: Yes (frequently joined)
```
</Accordion>

<Accordion title="Sensitive Data">
Flag sensitive columns clearly:

```markdown
### Column: credit_card_last_4
- Type: VARCHAR(4)
- Sensitivity: RESTRICTED (PII)
- Masking: Masked as '****' for non-admin users
- Encryption: At rest (encrypted column)
- Audit: All access logged and monitored
- Compliance: PCI DSS requirement
- Usage: Payment processing, fraud detection
```
</Accordion>

<Accordion title="Calculated/Derived">
Document calculated or derived columns:

```markdown
### Column: account_age_days
- Type: INTEGER
- Calculated: EXTRACT(DAY FROM CURRENT_DATE - created_at)
- Refresh: Real-time calculation on query
- Used For: Reporting, segmentation
- Performance: Indexed view for frequent queries
- Edge Case: Null if created_at is null
```
</Accordion>
</AccordionGroup>

### Schema Diagrams

Visual representations help communicate complex relationships.

**Entity Relationship Diagram (ERD) Documentation:**

```
customers (1) ──── (many) orders
     │
     ├── (1) ──── (many) billing_addresses
     │
     ├── (1) ──── (many) support_tickets
     │
     └── (1) ──── (many) activity_logs

orders (1) ──── (many) order_items
  │
  └── (many) ──── (1) products

Key:
- customers.id is PRIMARY KEY
- orders.customer_id FOREIGN KEY
- Each relationship documented with ON DELETE policy
```

**Using WhoDB Graph View:**

- Visualize schema relationships
- Export diagram for documentation
- Reference for new team members
- Identify missing relationships

## Query Documentation

### Query Repository Structure

Organize queries for easy discovery and maintenance.

**Repository Organization:**

```
queries/
├── README.md                          (overview and guidelines)
├── analytics/
│   ├── README.md
│   ├── revenue_by_region.sql
│   ├── customer_cohort_analysis.sql
│   ├── monthly_metrics.sql
│   └── .versions/
│       ├── monthly_metrics_v1.sql
│       └── monthly_metrics_v2.sql
├── reporting/
│   ├── README.md
│   ├── sales_dashboard_data.sql
│   ├── customer_insights.sql
│   └── inventory_status.sql
├── operational/
│   ├── README.md
│   ├── health_checks.sql
│   ├── data_cleanup.sql
│   └── orphaned_records_finder.sql
└── monitoring/
    ├── README.md
    ├── slow_queries.sql
    ├── connection_pool_status.sql
    └── table_growth_trends.sql
```

### Query Documentation Standard

<Accordion title="Query Documentation Components">
<Accordion title="Header Section">
Every query should start with complete metadata:

```sql
-- ============================================================================
-- Query: Daily Revenue Summary by Region
-- Purpose: Generate daily revenue breakdown for finance team reporting
-- ============================================================================
-- Author: Analytics Team (analytics@example.com)
-- Created: 2024-01-15
-- Last Updated: 2024-01-20 by Sarah Chen
-- Version: 2.0
--
-- Description:
--   Calculates total daily revenue by sales region for executive dashboard.
--   Includes transaction counts and average order values.
--   Updated to exclude test orders and refunded transactions.
--
-- Frequency: Daily (1 PM UTC)
-- Expected Runtime: 8-12 seconds on production
-- Output Rows: ~50-100 (one per region per day)
--
-- Dependencies:
--   - tables: sales_transactions, customers, products
--   - functions: get_fiscal_date()
--   - materialized views: mv_customer_segments (refreshed daily)
--
-- Historical Changes:
--   v2.0 (2024-01-20): Excluded test orders, optimized joins
--   v1.5 (2024-01-10): Added return order filtering
--   v1.0 (2024-01-01): Initial version
-- ============================================================================
```
</Accordion>

<Accordion title="Parameters Section">
Document all query parameters:

```sql
-- PARAMETERS
-- ============================================================================
-- @start_date     [REQUIRED]  Start of reporting period (YYYY-MM-DD)
-- @end_date       [REQUIRED]  End of reporting period (YYYY-MM-DD)
-- @region_filter  [OPTIONAL]  Specific region or NULL for all (default: NULL)
-- @exclude_test   [OPTIONAL]  Exclude test transactions (default: true)
--
-- USAGE EXAMPLES:
--   SELECT * FROM daily_revenue_by_region
--   WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
--     AND (@region_filter IS NULL OR region = @region_filter)
--
-- PERFORMANCE NOTES:
--   - Add index on (sales_date, region) for production
--   - Query is slowest first week of month (full historical scan)
-- ============================================================================
```
</Accordion>

<Accordion title="Business Logic Notes">
Explain important business rules:

```sql
-- BUSINESS RULES
-- ============================================================================
-- Revenue Definition:
--   - Includes: Base product price + applicable taxes + shipping
--   - Excludes: Discounts applied at checkout, refunds
--   - Partial Refunds: Counted as 50% of original order amount
--   - Early Payment Discount: Applied in revenue (not excluded)
--
-- Test Data Handling:
--   - Test orders identified by customer email: *@test-internal.com
--   - Typically 10-15 test orders per day, excluded by default
--
-- Regional Assignment:
--   - Based on customer.billing_region (NOT shipping_region)
--   - "UNASSIGNED" region for international orders without region
-- ============================================================================
```
</Accordion>

<Accordion title="Performance Notes">
Include optimization and performance information:

```sql
-- PERFORMANCE CHARACTERISTICS
-- ============================================================================
-- Query Plan:
--   1. Index scan on sales_transactions(sales_date, region)
--   2. Hash join to customers on customer_id
--   3. Filter and aggregation (streaming)
--
-- Performance Baseline:
--   - Full year query (365 days): 12 seconds
--   - Single month query: 2 seconds
--   - Single day query: 0.2 seconds
--
-- Potential Issues:
--   - Slow if transaction volume grows > 2M records/day
--   - Consider partitioning by month if 1M+ rows
--   - Disk I/O intensive during backup windows
--
-- Optimization Recommendations:
--   - Filter by date first (use index)
--   - Avoid ORDER BY on large sets (client-side sorting)
--   - Use LIMIT for testing before full run
-- ============================================================================
```
</Accordion>

<Accordion title="Change Log">
Track query evolution:

```sql
-- CHANGE LOG
-- ============================================================================
-- v2.0 (2024-01-20) - Sarah Chen
--   - Excluded test orders from calculations
--   - Fixed double-counting of multi-region orders
--   - Performance improved from 25s to 8s
--   - Breaking Change: Now requires @start_date parameter
--
-- v1.5 (2024-01-10) - Alex Rodriguez
--   - Added return order filtering (exclude from revenue)
--   - New column: refund_rate calculation
--
-- v1.0 (2024-01-01) - Analytics Team
--   - Initial implementation
--   - Baseline: 45 seconds for full year
-- ============================================================================
```
</Accordion>

<Accordion title="Related Queries">
Cross-reference related queries:

```sql
-- RELATED QUERIES
-- ============================================================================
-- Similar Queries:
--   - monthly_revenue_summary.sql (aggregated monthly view)
--   - customer_lifetime_value.sql (per-customer revenue)
--   - regional_performance_analysis.sql (detailed regional breakdown)
--
-- Dependent Queries:
--   - executive_dashboard.sql (uses this for KPI dashboard)
--   - finance_reconciliation.sql (uses for monthly close)
--
-- Successor Queries:
--   - daily_revenue_by_region_v3.sql (planned enhancement)
-- ============================================================================
```
</Accordion>
</Accordion>

### Quick Reference for Query Users

<AccordionGroup>
<Accordion title="When to Use This Query">
Help users understand appropriate use cases:

```
When to Use:
- Daily revenue reporting for finance
- Regional performance comparisons
- Month-over-month trends
- Executive dashboard KPI generation

When NOT to Use:
- Real-time revenue tracking (data delayed 1 hour)
- Detailed transaction-level analysis
- Customer-specific revenue analysis
- Refund/return detailed tracking
```
</Accordion>

<Accordion title="Common Mistakes to Avoid">
Document pitfalls users encounter:

```
Common Mistakes:
- Forgetting to set @start_date and @end_date
  → Results will be empty or only show current day

- Filtering on @region_filter = 'north'
  → Region names are uppercase: 'NORTH'
  → Use NULL to get all regions, not 'ALL'

- Running full year without LIMIT
  → Runs for 30+ seconds, may timeout
  → First run-through on range, then full query

- Assuming NULL values are zero
  → Missing data in some regions shows NULL
  → Must handle in reporting tool or use COALESCE
```
</Accordion>

<Accordion title="Troubleshooting">
Include solutions to common issues:

```
Query Times Out:
  Problem: Query exceeds 30 second timeout
  Solution: Add date filters for smaller date range
  Example: Use single month instead of full year

Query Returns Unexpected Results:
  Problem: Row count doesn't match previous runs
  Solution: Check if test orders included
  Debug: Add WHERE exclude_test = false to see test data

Query Uses Wrong Index:
  Problem: Query runs slower than expected
  Solution: Run ANALYZE on tables first
  Force Index: Add FORCE INDEX (idx_sales_date_region)
```
</Accordion>
</AccordionGroup>

## Operational Runbooks

### Creating Runbooks for Common Tasks

Runbooks are step-by-step procedures for recurring operational tasks.

**Runbook Template:**

```markdown
# Runbook: Monthly Data Refresh Procedure

## Overview
This runbook describes the monthly data refresh process that imports external data,
validates integrity, and updates reporting tables.

**Frequency**: Monthly, first Tuesday at 2 AM UTC
**Duration**: 15-45 minutes
**Owner**: Data Engineering Team

## Pre-Execution Checklist
- [ ] Scheduled backup completed successfully
- [ ] Development/staging environment available for testing
- [ ] All team members notified of execution
- [ ] Rollback plan reviewed and tested
- [ ] External data source confirmed available
- [ ] Database resources monitored and available

## Step-by-Step Procedure

### Phase 1: Preparation (5 minutes)
1. Connect to production database as data_engineer role
2. Create backup of staging schema:
   ```sql
   CREATE SCHEMA staging_backup_$(date +%Y%m%d) AS SELECT * FROM staging;
   ```
3. Verify disk space > 50GB available:
   ```sql
   SELECT pg_database_size('myapp') as current_size;
   ```
4. Check for long-running queries:
   ```sql
   SELECT pid, usename, query, query_start
   FROM pg_stat_activity
   WHERE state = 'active'
     AND query_start < NOW() - INTERVAL '5 minutes'
   ORDER BY query_start;
   ```

### Phase 2: Data Import (10-20 minutes)
1. Download external data file from SFTP
   ```bash
   sftp -i key.pem user@sftp.example.com
   cd /exports
   get customer_data_$(date +%Y%m%d).csv
   ```
2. Load data into staging table:
   ```sql
   COPY staging.customer_import
   FROM '/tmp/customer_data_20240115.csv'
   WITH (FORMAT csv, HEADER true, DELIMITER ',');
   ```
3. Monitor import progress:
   ```sql
   SELECT COUNT(*) as imported_count FROM staging.customer_import;
   ```

### Phase 3: Data Validation (10-15 minutes)
1. Run validation checks:
   ```sql
   CALL staging.validate_imported_data();
   ```
2. Compare row counts:
   ```sql
   SELECT
     (SELECT COUNT(*) FROM staging.customer_import) as imported,
     (SELECT COUNT(*) FROM staging.customer_current) as previous;
   ```
3. Verify data quality:
   ```sql
   SELECT * FROM staging.data_quality_report
   WHERE validation_status = 'FAILED';
   ```

### Phase 4: Integration (5-10 minutes)
1. Begin transaction:
   ```sql
   BEGIN;
   ```
2. Backup current production table:
   ```sql
   CREATE TABLE customer_backup_$(date +%Y%m%d) AS
   SELECT * FROM customer;
   ```
3. Swap staging data to production:
   ```sql
   DELETE FROM customer;
   INSERT INTO customer SELECT * FROM staging.customer_import;
   ```
4. Update refresh timestamp:
   ```sql
   UPDATE database_metadata
   SET last_refresh = CURRENT_TIMESTAMP
   WHERE table_name = 'customer';
   ```
5. Commit changes:
   ```sql
   COMMIT;
   ```

### Phase 5: Verification (5 minutes)
1. Verify production data integrity:
   ```sql
   SELECT * FROM public.integrity_check_results();
   ```
2. Run dependent reporting queries:
   ```sql
   SELECT COUNT(*) FROM daily_revenue_summary;
   ```
3. Check for orphaned records:
   ```sql
   SELECT COUNT(*) FROM customer c
   LEFT JOIN orders o ON c.id = o.customer_id
   WHERE o.id IS NULL AND o.id IS NOT NULL;
   ```

### Phase 6: Post-Execution (5 minutes)
1. Archive import file
2. Notify stakeholders of successful completion
3. Monitor application logs for errors
4. Retain backup for 7 days minimum
5. Document actual execution time

## Success Criteria
- [ ] Row count matches expected import count (within 5%)
- [ ] All data quality checks pass
- [ ] Production queries run in baseline time
- [ ] No errors in application logs
- [ ] Stakeholders confirm data appears correct

## Troubleshooting

### Issue: Data import hangs
**Solution**: Check disk space, increase work_mem parameter
```sql
SET work_mem = '512MB';
```

### Issue: Data validation fails
**Solution**: Compare with previous month's data
```sql
SELECT * FROM staging.validation_errors
ORDER BY error_type, error_count DESC;
```

### Issue: Need to rollback
**Solution**: Restore from backup table
```sql
BEGIN;
DELETE FROM customer;
INSERT INTO customer SELECT * FROM customer_backup_20240115;
COMMIT;
```

## Post-Incident Review
Document any issues that occurred and actions taken.
Template:
- What went wrong?
- Root cause?
- How was it resolved?
- Preventive measures for future?
- Lessons learned?
```

## Health Check Runbook

### Daily Health Checks

```markdown
# Daily Health Check Runbook

**Time**: 8 AM UTC each business day
**Duration**: 10-15 minutes
**Responsible**: On-call DBA

## Checks to Perform

### 1. Database Connectivity
```sql
SELECT version();
SELECT current_database();
```
Expected: Connection successful, production database active

### 2. Disk Space Monitoring
```sql
SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
WHERE datname NOT IN ('postgres', 'template0', 'template1');
```
Action: Alert if usage > 80% of available space

### 3. Connection Pool Status
```sql
SELECT
  COUNT(*) as total_connections,
  state,
  COUNT(*) as count
FROM pg_stat_activity
GROUP BY state;
```
Expected: Active connections < 50, Idle connections < 10

### 4. Recent Errors
```sql
SELECT COUNT(*) FROM database_logs
WHERE level = 'ERROR'
  AND created_at > NOW() - INTERVAL '24 hours';
```
Action: Investigate if error count > baseline

### 5. Backup Verification
```bash
ls -lh /backups/latest/
```
Expected: Latest backup completed in last 26 hours
```

## Incident Response Runbook

### Database Down Investigation

```markdown
# Incident: Database Unreachable

**Severity**: CRITICAL
**Impact**: No database access, application offline
**Time to Resolution Target**: 15 minutes

## Immediate Actions (First 5 minutes)

1. [ ] Confirm database is actually down
   ```bash
   telnet db-host 5432
   pg_isready -h db-host
   ```

2. [ ] Check connectivity from multiple locations

3. [ ] Check service status
   ```bash
   systemctl status postgresql
   sudo ps aux | grep postgres
   ```

4. [ ] Check logs
   ```bash
   tail -f /var/log/postgresql/postgresql.log
   ```

5. [ ] Notify team immediately
   - Post in incident channel
   - Alert on-call manager
   - Update status page

## Investigation (5-15 minutes)

- [ ] Check system resources (CPU, memory, disk)
- [ ] Review recent connections
- [ ] Check for locks or hanging transactions
- [ ] Review recent changes
- [ ] Contact hosting provider if infrastructure issue

## Recovery Steps

- [ ] Restart PostgreSQL service
- [ ] Verify replication status (if applicable)
- [ ] Verify backups integrity
- [ ] Test connectivity

## Post-Incident

- [ ] Document timeline of events
- [ ] Identify root cause
- [ ] Create preventive measures
- [ ] Schedule follow-up review
```

## Documentation Maintenance

### Keeping Documentation Current

<Warning>
Outdated documentation is worse than no documentation. Establish regular review processes to keep all documentation accurate and current.
</Warning>

**Documentation Review Schedule:**

```
Monthly:
  - Query documentation (update query versions)
  - Performance baselines (compare actual vs documented)
  - Runbook procedures (verify still accurate)

Quarterly:
  - Schema documentation (verify all tables documented)
  - Role documentation (check if roles still exist)
  - Access policies (verify implementations)

Annually:
  - Comprehensive documentation audit
  - Retire obsolete queries and procedures
  - Update historical information
  - Verify links and cross-references
```

### Documentation Tools and Templates

**File Organization:**

```
docs/
├── README.md              (overview, table of contents)
├── schemas/
│   ├── schema_overview.md
│   ├── entity_relationships.md
│   └── tables/
│       ├── customers.md
│       ├── orders.md
│       └── products.md
├── queries/
│   ├── query_guide.md
│   └── library/
├── procedures/
│   ├── daily_maintenance.md
│   ├── monthly_refresh.md
│   ├── incident_response.md
│   └── disaster_recovery.md
└── standards/
    ├── naming_conventions.md
    ├── documentation_standards.md
    └── security_policies.md
```

## Documentation Checklist

**Schema Documentation:**

- [ ] Every table has documented purpose
- [ ] Every significant column is documented
- [ ] Foreign keys explained with relationships
- [ ] Data retention policies documented
- [ ] Update frequency specified
- [ ] Access restrictions noted
- [ ] Known issues documented
- [ ] Related tables cross-referenced

**Query Documentation:**

- [ ] Purpose and business logic clear
- [ ] Parameters documented with examples
- [ ] Expected runtime and row count documented
- [ ] Performance baseline established
- [ ] Change history maintained
- [ ] Related queries cross-referenced
- [ ] Common mistakes documented
- [ ] Troubleshooting guide provided

**Operational Documentation:**

- [ ] Daily health check procedure documented
- [ ] Monthly maintenance procedure documented
- [ ] Emergency response procedures ready
- [ ] Disaster recovery tested
- [ ] Escalation procedures defined
- [ ] Contact information current
- [ ] Success criteria clear
- [ ] Troubleshooting steps included

**Ongoing Maintenance:**

- [ ] Documentation reviewed monthly
- [ ] Links and references updated
- [ ] Obsolete procedures removed
- [ ] New procedures added promptly
- [ ] Team knowledge captured
- [ ] Runbooks tested regularly
- [ ] Feedback incorporated
- [ ] Version history maintained

## Summary

Comprehensive documentation transforms database management from tribal knowledge into repeatable, scalable practice. Well-documented schemas help new team members understand data structure quickly. Documented queries enable safe code reuse and prevent duplicated effort. Operational runbooks ensure consistency and reduce incident response time. By investing in thorough documentation and maintaining it diligently, you create an information resource that multiplies team productivity and ensures continuity even as team members change. WhoDB's ability to export queries and track history makes it an excellent foundation for building and maintaining your documentation repository.
